package cn.shop.gateway.db;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.shop.gateway.Config;
import cn.shop.gateway.model.MerchantGamePartition;
import cn.shop.gateway.model.MerchantGameTemplate;
import cn.shop.gateway.model.MerchantUser;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Collections;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * @author : 李一涵
 * @date : Created in 2021-12-9 15:27
 * @description :
 */
public class DbUtil {

    private final static Logger LOG = LoggerFactory.getLogger(DbUtil.class);

    private static Db DB = null;

    public static void init() throws Exception {
        DB = Db.use(DSFactory.create(Config.getSetting()).getDataSource("HikariCP"));
        File file = new File(Objects.requireNonNull(Config.getString("lock.file")));
        if (!file.exists()) {
            drop();
            initGamePartition();
            initMerchantInfo();
            initGameTemplate();
            initSendCount();
            initOrderCheck();
            initFailOrder();
            initSecret();
            file.createNewFile();
        }
    }

    private static void drop() throws Exception {
        DB.execute("drop table IF EXISTS " + MerchantUser.Table.NAME);
        DB.execute("drop table IF EXISTS " + MerchantGameTemplate.Table.NAME);
        DB.execute("drop table IF EXISTS " + MerchantGamePartition.Table.NAME);
        DB.execute("drop table IF EXISTS send_count");
        DB.execute("drop table IF EXISTS order_check");
        DB.execute("drop table IF EXISTS fail_order");
        DB.execute("drop table IF EXISTS gateway_secret");
    }

    private static void initGamePartition() throws Exception {
        DB.execute(MerchantGamePartition.Table.CREATE_SQL);
    }

    private static void initMerchantInfo() throws Exception {
        DB.execute(MerchantUser.Table.CREATE_SQL);
    }

    private static void initGameTemplate() throws Exception {
        DB.execute(MerchantGameTemplate.Table.CREATE_SQL);
    }

    public static void initSendCount() throws SQLException {
        DB.execute("CREATE TABLE send_count(" +
                "id bigint KEY NOT NULL," +
                "allCount bigint NOT NULL," +
                "successCount bigint NOT NULL," +
                "failCount bigint NOT NULL)");
    }

    public static void initOrderCheck() throws SQLException {
        DB.execute("CREATE TABLE order_check(" +
                "id bigint KEY NOT NULL," +
                "time text NOT NULL)");
    }

    public static void initFailOrder() throws SQLException {
        DB.execute("CREATE TABLE fail_order(" +
                "folder text NOT NULL," +
                "json text NOT NULL)");
    }

    public static void initSecret() throws SQLException {
        DB.execute("CREATE TABLE gateway_secret(" +
                "id bigint KEY NOT NULL," +
                "gateway_secret text NOT NULL)");
    }

    public static void insertMerchantInfo(MerchantUser user) throws Exception {
        DB.tx(tx -> {
            tx.execute("delete from " + MerchantUser.Table.NAME);
            tx.insert(Entity.create(MerchantUser.Table.NAME)
                    .set(MerchantUser.Field.ID, user.getId())
                    .set(MerchantUser.Field.ACCOUNT, user.getAccount())
                    .set(MerchantUser.Field.PASSWORD, user.getPassword()));
        });
    }

    public static Optional<Long> getMerchantInfo() throws Exception{
        return DB.findAll(MerchantUser.Table.NAME).stream()
                .map(e -> e.getLong(MerchantUser.Field.ID))
                .findFirst();
    }

    public static void insertGameTemplate(MerchantGameTemplate gameTemplate) throws Exception {
        DB.tx(tx -> {
            tx.execute("delete from " + MerchantGameTemplate.Table.NAME);
            tx.insert(Entity.create(MerchantGameTemplate.Table.NAME)
                    .set(MerchantGameTemplate.Field.ID, gameTemplate.getId())
                    .set(MerchantGameTemplate.Field.NAME, gameTemplate.getName())
                    .set(MerchantGameTemplate.Field.RATIO, gameTemplate.getRatio())
                    .set(MerchantGameTemplate.Field.CURRENCY, gameTemplate.getCurrency()));
        });
    }

    public static Optional<Long> getGameTemplate() throws Exception {
        return DB.findAll(MerchantGameTemplate.Table.NAME).stream()
                .map(e -> e.getLong(MerchantGameTemplate.Field.ID))
                .findFirst();
    }

    public static Entity getGameTemplateById() {
        try {
            return DB.get(Entity.create(MerchantGameTemplate.Table.NAME));
        } catch (Exception e) {
            return Entity.create();
        }
    }

    public static int putGamePartition(MerchantGamePartition partition) throws Exception {
        return DB.insertOrUpdate(Entity.create(MerchantGamePartition.Table.NAME)
                .set(MerchantGamePartition.Field.ID, partition.getId())
                .set(MerchantGamePartition.Field.NAME, partition.getName())
                .set(MerchantGamePartition.Field.FOLDER, StrUtil.blankToDefault(partition.getServerFolder(), "").toUpperCase()),
                MerchantGamePartition.Field.ID);
    }

    public static long getGamePartitionByName(String name) {
        try {
            Optional<MerchantGamePartition> partition = DB.find(Entity.create(MerchantGamePartition.Table.NAME)
                            .set(MerchantGamePartition.Field.NAME, name), MerchantGamePartition.class)
                    .stream()
                    .findFirst();
            return partition.isPresent() ? partition.get().getId() : 0;
        } catch (Exception e) {
            LOG.error("查询分区id出错", e);
            return 0;
        }
    }

    public static long getGamePartitionByFolder(String path) {
        try {
            Optional<MerchantGamePartition> partition = DB.find(Entity.create(MerchantGamePartition.Table.NAME)
                            .set(MerchantGamePartition.Field.FOLDER, path), MerchantGamePartition.class)
                    .stream()
                    .findFirst();
            return partition.isPresent() ? partition.get().getId() : 0;
        } catch (Exception e) {
            LOG.error("查询分区id出错", e);
            return 0;
        }
    }

    public static List<Entity> getGamePartitions() {
        try {
            return DB.findAll(Entity.create(MerchantGamePartition.Table.NAME));
        } catch (SQLException e) {
            LOG.error("查询分区出错", e);
            return Collections.emptyList();
        }
    }

    public static Entity getGamePartitionById(long id) {
        try {
            return DB.get(Entity.create(MerchantGamePartition.Table.NAME).set(MerchantGamePartition.Field.ID, id));
        } catch (Exception e) {
            LOG.error("查询分区出错", e);
            return Entity.create();
        }
    }

    public static List<Entity> removeGamePartition(Long[] ids) {
        if (ids == null || ids.length < 1) {
            return null;
        }
        try {
            Entity query = Entity.create(MerchantGamePartition.Table.NAME).set(MerchantGamePartition.Field.ID, ids);
            List<Entity> list = DB.findAll(query);
            long count = DB.del(query);
            return count > 0 ? list : null;
        } catch (Exception e) {
            LOG.error("删除分区出错", e);
            return null;
        }
    }

    public static void updateSendCount(long allCount, long successCount, long failCount) {
        try {
            DB.insertOrUpdate(Entity.create("send_count")
                    .set("allCount", allCount)
                    .set("successCount", successCount)
                    .set("failCount", failCount)
                    .set("id", 1), "id");
        } catch (SQLException e) {
            LOG.error("更新次数出错", e);
        }
    }

    public static Entity getSendCount() throws SQLException {
        return DB.get(Entity.create("send_count").set("id", 1));
    }

    public static void saveOrderCheck(long id) {
        try {
            DB.insert(Entity.create("order_check").set("id", id).set("time", LocalDateTime.now()));
        } catch (SQLException e) {
            LOG.error("更新订单下发记录出错, {}", id, e);
        }
    }

    public static Entity getOrderCheck(String id) throws SQLException {
        return DB.get(Entity.create("order_check").set("id", id));
    }

    public static void saveFailOrder(String folder, List<String> orderText) {
        try {
            String json = String.join("#", orderText);
            DB.insert(Entity.create("fail_order").set("json", json).set("folder", folder));
        } catch (Exception e) {
            LOG.error("保存失败订单出错，{}", orderText, e);
        }
    }

    public static List<Entity> getFailOrders() {
        try {
            List<Entity> list = DB.findAll("fail_order");
            clearFailOrder();
            return list;
        } catch (Exception e) {
            LOG.error("查询失败订单出错", e);
            return Collections.emptyList();
        }
    }

    private static void clearFailOrder() throws SQLException {
        DB.execute("delete from fail_order");
    }

    public static void saveSecret(String secret) {
        try {
            DB.insertOrUpdate(Entity.create("gateway_secret")
                    .set("gateway_secret", secret)
                    .set("id", 1), "id");
        } catch (SQLException e) {
            LOG.error("更新秘钥出错", e);
        }
    }

    public static String getSecret() {
        try {
            return DB.get(Entity.create("gateway_secret").set("id", 1)).getStr("gateway_secret");
        } catch (Exception e) {
            return null;
        }
    }

}
